﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;
namespace LC.Device.Common
{
    public class ExcelHelper : IDisposable
    {
        private string fileName = null; //文件名
        private HSSFWorkbook workbook = null;
        private FileStream fs = null;
        private bool disposed;

        public ExcelHelper()
        {
            disposed = false;
        }

        public ExcelHelper(string fileName)
        {
            this.fileName = fileName;
            disposed = false;
        }

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            HSSFSheet sheet = null;

            fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (fileName.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                if (isColumnWritten == true) //写入DataTable的列名
                {
                    HSSFRow row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }

                for (i = 0; i < data.Rows.Count; ++i)
                {
                    HSSFRow row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }
                workbook.Write(fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return -1;
            }
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
        {
            HSSFSheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                }
                if (sheet == null)
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    HSSFRow firstRow = sheet.GetRow(0);
                    //int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                    int cellCount = firstRow.PhysicalNumberOfCells;
                    
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            if (!data.Columns.Contains(firstRow.GetCell(i).StringCellValue))
                            {
                                DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
                                data.Columns.Add(column);
                            }
                            else
                            {
                                DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue + "1");
                                data.Columns.Add(column);
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }



                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                   
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        HSSFRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            HSSFCell cell = row.GetCell(j);
                            if (j == 13)
                            {
                                string num = string.Empty;
                            }
                            if (cell != null)
                            { //同理，没有数据的单元格都默认是null
                                //dataRow[j] = row.GetCell(j).ToString();
                                //读取Excel格式，根据格式读取数据类型


                                switch (cell.CellType)
                                {
                                    case HSSFCell.CELL_TYPE_BLANK: //空数据类型处理
                                        dataRow[j] = "";
                                        break;
                                    case HSSFCell.CELL_TYPE_STRING: //字符串类型
                                        dataRow[j] = cell.StringCellValue;
                                        break;
                                    case HSSFCell.CELL_TYPE_NUMERIC: //数字类型                                   
                                        //                                     if (DateUtil.IsValidExcelDate(cell.NumericCellValue))
                                        if (HSSFDateUtil.IsCellDateFormatted(cell))
                                        {
                                            string date = cell.DateCellValue.ToString("yyyy-MM-dd");
                                            dataRow[j] = date;
                                        }
                                        else if (j == 13)
                                        {
                                            string num = cell.NumericCellValue.ToString();
                                            dataRow[j] = num;
                                        }
                                        else
                                        {
                                            string num = cell.NumericCellValue.ToString();
                                            dataRow[j] = num;
                                        }

                                        //DateUtil.IsValidExcelDate(cell.NumericCellValue);
                                        //string num=cell.ToString();
                                        //dataRow[j] = num;
                                        break;
                                    case HSSFCell.CELL_TYPE_FORMULA:
                                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
                                        if (e.Evaluate(cell).CellType == HSSFCell.CELL_TYPE_NUMERIC)
                                        {
                                            dataRow[j] = e.Evaluate(cell).NumberValue;
                                        }
                                        else
                                        {
                                            dataRow[j] = e.Evaluate(cell).StringValue;
                                        }
                                        break;
                                    default:
                                        dataRow[j] = "";
                                        break;
                                }


                                //if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
                                //{
                                //    short format = cell.getCellStyle().getDataFormat();
                                //    SimpleDateFormat sdf = null;
                                //    if (format == 14 || format == 31 || format == 57 || format == 58)
                                //    {
                                //        //日期
                                //        sdf = new SimpleDateFormat("yyyy-MM-dd");
                                //    }
                                //    else if (format == 20 || format == 32)
                                //    {
                                //        //时间
                                //        sdf = new SimpleDateFormat("HH:mm");
                                //    }
                                //    double value = cell.getNumericCellValue();
                                //    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                                //    result = sdf.format(date);
                                //}







                            }
                        }
                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (fs != null)
                        fs.Close();
                }

                fs = null;
                disposed = true;
            }
        }

        /// <summary>
        /// 由列模型，DataTable生成电子表格
        /// </summary>
        /// <param name="ExcelColumn"></param>
        /// <param name="dt"></param>
        /// <param name="prefix"></param>
        /// <param name="ext"></param>
        /// <param name="fileName"></param>
        public void ExportExcel(List<ExcelColumn> ExcelColumn, DataTable dt, string fileName, string ext)
        {
            if (ExcelColumn.Count == 0)
            {
                return;
            }
            if (string.IsNullOrEmpty(fileName))
            {
                fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "." + ext;
            }
            else
            {
                fileName = fileName + "." + ext;
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            if (ext == "xls")
            {
                workbook = new HSSFWorkbook();
            }
            HSSFSheet sheet = workbook.CreateSheet("sheet1");//取第一个表

            //加列头
            if (ExcelColumn.Count() > 0)
            {
                int index = 0;
                HSSFRow excelHeadRow = sheet.CreateRow(0);
                foreach (ExcelColumn column in ExcelColumn)
                {
                    excelHeadRow.CreateCell(index).SetCellValue(column.ColumnName);
                    sheet.SetColumnWidth(index, column.Width);
                    index++;
                }

            }
            if (dt != null && dt.Rows.Count > 0)
            {
                //向单元格添值
                int index = 1;
                foreach (DataRow dr in dt.Rows)
                {

                    int j = 0;
                    HSSFRow excelRow = sheet.CreateRow(index);
                    foreach (ExcelColumn column in ExcelColumn)
                    {
                        string fileCode = column.ColumnCode;
                        string cellValue = string.Empty;
                        if (dr[fileCode] != null)
                        {
                            cellValue = dr[fileCode].ToString();
                            if (Utility.IsDateTime(cellValue) && cellValue == "1990-01-01 00:00:00")
                            {
                                cellValue = "";
                            }

                            if (dt.Columns[fileCode].DataType.Name == "Decimal" || dt.Columns[fileCode].DataType.Name == "Int32")
                            {

                                decimal cellVal = Math.Round(Utility.ToDecimal(dr[fileCode].ToString(), 0), 0);

                                excelRow.CreateCell(j).SetCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                excelRow.CreateCell(j).SetCellValue((Double)cellVal);
                            }
                            else
                            {

                                excelRow.CreateCell(j).SetCellType(HSSFCell.CELL_TYPE_STRING);
                                excelRow.CreateCell(j).SetCellValue(cellValue);
                            }

                        }
                        else
                        {

                            excelRow.CreateCell(j).SetCellType(HSSFCell.CELL_TYPE_STRING);
                            excelRow.CreateCell(j).SetCellValue(cellValue);
                        }
                        j++;
                    }
                    index++;
                }
            }
            string filePath = GetFilePath(fileName);
            using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite))
            {
                workbook.Write(fs);
            }
        }


        /// <summary>
        /// 由列模型，DataTable生成电子表格
        /// </summary>
        /// <param name="ExcelColumn"></param>
        /// <param name="dt"></param>
        /// <param name="prefix"></param>
        /// <param name="ext"></param>
        /// <param name="fileName"></param>
        public void ExportExcel(List<ExcelColumn> ExcelColumn, DataTable dt, string prefix, string ext, out string fileName)
        {
            fileName = string.Empty;
            if (ExcelColumn.Count == 0)
            {
                return;
            }
            fileName = prefix + DateTime.Now.ToString("yyyyMMddHHmmss") + "." + ext;
            HSSFWorkbook workbook = new HSSFWorkbook();
            if (ext == "xls")
            {
                workbook = new HSSFWorkbook();
            }

            HSSFSheet sheet = workbook.CreateSheet("sheet1");//取第一个表
            //加列头
            if (ExcelColumn.Count() > 0)
            {
                int index = 0;
                HSSFRow excelHeadRow = sheet.CreateRow(0);
                foreach (ExcelColumn column in ExcelColumn)
                {
                    if (column.CellType == 0)
                    {
                        excelHeadRow.CreateCell(index).SetCellType(HSSFCell.CELL_TYPE_STRING);
                    }
                    else
                    {
                        excelHeadRow.CreateCell(index).SetCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    }
                    excelHeadRow.CreateCell(index).SetCellValue(column.ColumnName);
                    sheet.SetColumnWidth(index, column.Width);
                    index++;
                }

            }
            if (dt != null && dt.Rows.Count > 0)
            {
                //向单元格添值
                int index = 1;
                foreach (DataRow dr in dt.Rows)
                {

                    int j = 0;
                    HSSFRow excelRow = sheet.CreateRow(index);
                    foreach (ExcelColumn column in ExcelColumn)
                    {
                        string fileCode = column.ColumnCode;
                        string cellValue = string.Empty;
                        if (dr[fileCode] != null)
                        {
                            cellValue = dr[fileCode].ToString();
                            if (Utility.IsDateTime(cellValue) && cellValue == "1990-01-01 00:00:00")
                            {
                                cellValue = "";
                            }
                        }
                        HSSFCell newCell = excelRow.CreateCell(j);
                        //if (column.CellType == 0)
                        //{
                        //    excelRow.CreateCell(j).SetCellType(CellType.String);
                        //}
                        //else
                        //{
                        //    excelRow.CreateCell(j).SetCellType(CellType.Numeric);
                        //    int index = dt.Rows.IndexOf(dr); //index 为索引值
                        //    dt.Rows[index]
                        //}

                        switch (dt.Columns[column.ColumnCode].DataType.ToString())
                        {
                            case "System.String"://字符串类型   
                                newCell.SetCellValue(cellValue);
                                break;
                            case "System.DateTime"://日期类型   
                                DateTime dateV;
                                DateTime.TryParse(cellValue, out dateV);
                                newCell.SetCellValue(dateV);
                                newCell.CellStyle = Getcellstyle(workbook, stylexls.时间);//格式化显示   
                                break;
                            case "System.Boolean"://布尔型   
                                bool boolV = false;
                                bool.TryParse(cellValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型   
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(cellValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型   
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(cellValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值处理   
                                newCell.SetCellValue("");
                                break;
                            default:
                                newCell.SetCellValue("");
                                break;
                        }
                        //excelRow.CreateCell(j).SetCellValue(cellValue);
                        j++;
                    }
                    index++;
                }
            }
            string filePath = GetFilePath(fileName);
            try
            {
                using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite))
                {
                    workbook.Write(fs);
                }
            }
            catch (Exception ex)
            {
                Utility.ThrowSystemAbnormal(filePath + ex.ToString());
            }
        }

        /// <summary>
        /// 由列模型，DataTable生成电子表格
        /// </summary>
        /// <param name="ExcelColumn">表格列</param>
        /// <param name="dt">表格数据</param>
        /// <param name="prefix">文件名前缀</param>
        /// <param name="ext">excel扩展名</param>
        /// <param name="title">标题</param>
        /// <param name="fileName">生成的execle文件名</param>
        public void ExportExcel(List<ExcelColumn> ExcelColumn, DataTable dt, string prefix, string ext, string title, out string fileName)
        {
            fileName = string.Empty;
            if (ExcelColumn.Count == 0)
            {
                return;
            }
            fileName = prefix + DateTime.Now.ToString("yyyyMMddHHmmss") + "." + ext;
            HSSFWorkbook workbook = new HSSFWorkbook();
            if (ext == "xls")
            {
                workbook = new HSSFWorkbook();
            }
            //else if (ext == "xlsx")
            //{
            //    workbook = new XSSFWorkbook();
            //}
            HSSFSheet sheet = workbook.CreateSheet("sheet1");//取第一个表

            bool IsHasTitle = false;//是否有标题
            if (!string.IsNullOrWhiteSpace(title))
            {
                IsHasTitle = true;

                //合并单元格
                SetCellRangeAddress(sheet, 0, 0, 0, ExcelColumn.Count - 1);
                //插入标题
                HSSFRow excelHeadRow = sheet.CreateRow(0);
                HSSFCell excelCell = excelHeadRow.CreateCell(0);
                excelCell.SetCellValue(title);
                HSSFFont font = GetFontStyle(workbook, "宋体", new NPOI.HSSF.Util.HSSFColor.BLACK().GetIndex(), 16);
                excelCell.CellStyle = GetCellStyle(workbook, font, new NPOI.HSSF.Util.HSSFColor.BLACK().GetIndex(), NPOI.HSSF.UserModel.HSSFCellStyle.NO_FILL, new NPOI.HSSF.Util.HSSFColor.TAN().GetIndex(), NPOI.HSSF.UserModel.HSSFCellStyle.ALIGN_CENTER, NPOI.HSSF.UserModel.HSSFCellStyle.VERTICAL_CENTER);
            }

            //加列头
            if (ExcelColumn.Count() > 0)
            {
                int index = 0;
                HSSFRow excelHeadRow;
                if (IsHasTitle)
                {
                    excelHeadRow = sheet.CreateRow(1);
                }
                else
                {
                    excelHeadRow = sheet.CreateRow(0);
                }

                foreach (ExcelColumn column in ExcelColumn)
                {
                    excelHeadRow.CreateCell(index).SetCellValue(column.ColumnName);
                    sheet.SetColumnWidth(index, column.Width);
                    index++;
                }

            }
            if (dt != null && dt.Rows.Count > 0)
            {
                //向单元格添值
                int index = 1;
                if (IsHasTitle) index++;
                foreach (DataRow dr in dt.Rows)
                {

                    int j = 0;
                    HSSFRow excelRow = sheet.CreateRow(index);
                    foreach (ExcelColumn column in ExcelColumn)
                    {
                        string fileCode = column.ColumnCode;
                        string cellValue = string.Empty;
                        if (dr[fileCode] != null)
                        {
                            cellValue = dr[fileCode].ToString();
                            if (Utility.IsDateTime(cellValue) && cellValue == "1990-01-01 00:00:00")
                            {
                                cellValue = "";
                            }
                        }
                        excelRow.CreateCell(j).SetCellType(HSSFCell.CELL_TYPE_STRING);
                        excelRow.CreateCell(j).SetCellValue(cellValue);
                        j++;
                    }
                    index++;
                }
            }

            //SetCellRangeAddress(sheet, 0, 0, 0, ExcelColumn.Count - 1);

            string filePath = GetFilePath(fileName);
            try
            {
                using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite))
                {
                    workbook.Write(fs);
                }
            }
            catch (Exception ex)
            {
                Utility.ThrowSystemAbnormal(filePath + ex.ToString());
            }
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        private void SetCellRangeAddress(HSSFSheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            //NPOI.HSSF.Util.CellRangeAddress
            NPOI.HSSF.Util.CellRangeAddress cellRangeAddress = new NPOI.HSSF.Util.CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
        }

        /// <summary>
        /// 创建单元格字体的样式及大小
        /// </summary>
        /// <param name="hssfworkbook">Excel操作类</param>
        /// <param name="fontname">字体名</param>
        /// <param name="fontcolor">字体颜色</param>
        /// <param name="fontsize">字体大小</param>
        /// <returns></returns>
        private HSSFFont GetFontStyle(HSSFWorkbook hssfworkbook, string fontfamily, short fontcolor, int fontsize)
        {
            HSSFFont font1 = hssfworkbook.CreateFont();
            if (string.IsNullOrEmpty(fontfamily))
            {
                font1.FontName = fontfamily;
            }
            font1.Color = fontcolor;
            font1.IsItalic = false;
            font1.FontHeightInPoints = (short)fontsize;
            return font1;
        }

        /// <summary>
        /// 获取单元格样式
        /// </summary>
        /// <param name="hssfworkbook">Excel操作类</param>
        /// <param name="font">单元格字体</param>
        /// <param name="fillForegroundColor">图案的颜色</param>
        /// <param name="fillPattern">图案样式</param>
        /// <param name="fillBackgroundColor">单元格背景</param>
        /// <param name="ha">垂直对齐方式</param>
        /// <param name="va">垂直对齐方式</param>
        /// <returns></returns>
        private HSSFCellStyle GetCellStyle(HSSFWorkbook hssfworkbook, HSSFFont font, short fillForegroundColor, short fillPattern, short fillBackgroundColor, short ha, short va)
        {
            HSSFCellStyle cellstyle = hssfworkbook.CreateCellStyle();
            cellstyle.FillPattern = fillPattern;
            cellstyle.Alignment = ha;
            cellstyle.VerticalAlignment = va;
            if (fillForegroundColor != null)
            {
                //cellstyle.FillForegroundColor = fillForegroundColor.Indexed;
                cellstyle.FillForegroundColor = fillPattern;
            }
            if (fillBackgroundColor != null)
            {
                //cellstyle.FillBackgroundColor = fillBackgroundColor.Indexed;
                cellstyle.FillForegroundColor = fillPattern;
            }
            if (font != null)
            {
                cellstyle.SetFont(font);
            }
            //有边框

            cellstyle.BorderBottom = HSSFCellStyle.BORDER_THIN;
            cellstyle.BorderLeft = HSSFCellStyle.BORDER_THIN;
            cellstyle.BorderRight = HSSFCellStyle.BORDER_THIN;
            cellstyle.BorderTop = HSSFCellStyle.BORDER_THIN;
            return cellstyle;
        }

        #region 定义单元格常用到样式
        public HSSFCellStyle Getcellstyle(HSSFWorkbook wb, stylexls str)
        {
            HSSFCellStyle cellStyle = wb.CreateCellStyle();
            //定义几种字体
            //也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
            HSSFFont font12 = wb.CreateFont();
            font12.FontHeightInPoints = 10;
            font12.FontName = "微软雅黑";

            HSSFFont font = wb.CreateFont();
            font.FontName = "微软雅黑";
            //font.Underline = 1;下划线

            HSSFFont fontcolorblue = wb.CreateFont();
            fontcolorblue.Color = new NPOI.HSSF.Util.HSSFColor.GREEN().GetIndex();
            fontcolorblue.IsItalic = true;//下划线
            fontcolorblue.FontName = "微软雅黑";

            //边框
            cellStyle.BorderBottom = HSSFCellStyle.BORDER_DOTTED;

            // NPOI.SS.UserModel.BorderStyle.Dotted;
            cellStyle.BorderLeft = HSSFCellStyle.BORDER_HAIR;// NPOI.SS.UserModel.BorderStyle.Hair;
            cellStyle.BorderRight = HSSFCellStyle.BORDER_HAIR; // NPOI.SS.UserModel.BorderStyle.Hair;
            cellStyle.BorderTop = HSSFCellStyle.BORDER_DOTTED;//NPOI.SS.UserModel.BorderStyle.Dotted;
            //边框颜色
            cellStyle.BottomBorderColor = new NPOI.HSSF.Util.HSSFColor.GREEN().GetIndex();
            cellStyle.TopBorderColor = new NPOI.HSSF.Util.HSSFColor.GREEN().GetIndex();
            //背景图形，我没有用到过。感觉很丑
            //cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
            //cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
            cellStyle.FillForegroundColor = new NPOI.HSSF.Util.HSSFColor.WHITE().GetIndex();
            // cellStyle.FillPattern = FillPatternType.NO_FILL;
            cellStyle.FillBackgroundColor = new NPOI.HSSF.Util.HSSFColor.BLUE().GetIndex();
            //水平对齐
            cellStyle.Alignment = NPOI.HSSF.UserModel.HSSFTextbox.HORIZONTAL_ALIGNMENT_LEFT;//HSSFCellStyle.ho.UserModel.HorizontalAlignment.Left;
            //垂直对齐
            cellStyle.VerticalAlignment = NPOI.HSSF.UserModel.HSSFTextbox.VERTICAL_ALIGNMENT_CENTER;//VerticalAlignment.Center;
            //自动换行
            cellStyle.WrapText = true;
            //缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
            cellStyle.Indention = 0;
            //上面基本都是设共公的设置
            //下面列出了常用的字段类型
            switch (str)
            {
                case stylexls.头:
                    // cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
                    cellStyle.SetFont(font12);
                    break;
                case stylexls.时间:
                    HSSFDataFormat datastyle = wb.CreateDataFormat();
                    cellStyle.DataFormat = datastyle.GetFormat("yyyy-mm-dd");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.数字:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.钱:
                    HSSFDataFormat format = wb.CreateDataFormat();
                    cellStyle.DataFormat = format.GetFormat("￥#,##0");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.url:
                    fontcolorblue.Underline = HSSFFontFormatting.U_DOUBLE;//FontUnderlineType.Double;
                    cellStyle.SetFont(fontcolorblue);
                    break;
                case stylexls.百分比:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.中文大写:
                    HSSFDataFormat format1 = wb.CreateDataFormat();
                    cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.科学计数法:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.默认:
                    cellStyle.SetFont(font);
                    break;
            }
            return cellStyle;

        }
        #endregion

        private string GetFilePath(string fileName)
        {
            string webRoot = ConfigHelper.UploadFileRootDirectory;
            string dirPath = System.IO.Path.Combine(webRoot, "ExportExcel");
            if (System.IO.Directory.Exists(dirPath))
            {
                System.IO.Directory.CreateDirectory(dirPath);
            }
            string filePath = System.IO.Path.Combine(dirPath, fileName);
            if (System.IO.File.Exists(filePath))
            {
                System.IO.File.Delete(filePath);
            }
            return filePath;
        }

        /// <summary>
        /// 从EXCEL文件导入到DataTable
        /// </summary>
        /// <param name="excelFileStream">EXCEL文件流</param>
        /// <param name="sheetName">sheet名</param>
        /// <param name="headerRowIndex">标题行索引</param>
        /// <returns>返回DataTable</returns>
        public static DataTable ImportDataTableFromExcel(System.IO.Stream excelFileStream, int sheetIndex, int headerRowIndex)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelFileStream);//创建工作簿对象
            HSSFSheet sheet = workbook.GetSheetAt(sheetIndex);//获取参数指定的sheet
            DataTable table = new DataTable();
            HSSFRow headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;
            //读取并生成标题行，这里能成功执行
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            //逐个读取单元格                                  
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                HSSFRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                    }
                }
                table.Rows.Add(dataRow);
            }
            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }
    }

    /// <summary>
    /// 列模型
    /// </summary>
    public class ExcelColumn
    {
        /// <summary>
        /// 数据库字段名称
        /// </summary>
        public string ColumnCode { get; set; }
        /// <summary>
        /// 列表显示名称
        /// </summary>
        public string ColumnName { get; set; }
        /// <summary>
        /// 单元格类型
        /// </summary>
        public short CellType { get; set; }
        public ExcelColumn()
        {

        }
        public ExcelColumn(string ColumnCode, string ColumnName)
        {
            this.ColumnCode = ColumnCode;
            this.ColumnName = ColumnName;
            this.Width = 4000;
            this.CellType = 0;
        }
        public ExcelColumn(string ColumnCode, string ColumnName, short CellType)
        {
            this.ColumnCode = ColumnCode;
            this.ColumnName = ColumnName;
            this.Width = 4000;
            this.CellType = CellType;
        }

        private int _width = 4000;
        public int Width
        {
            get { return _width; }
            set { _width = value; }
        }
    }


    #region 定义单元格常用到样式的枚举
    public enum stylexls
    {
        头,
        url,
        时间,
        数字,
        钱,
        百分比,
        中文大写,
        科学计数法,
        默认
    }
    #endregion
}